﻿using GKQC.Common;
using GKQC.DbHelper.Dao;
using GKQC.DbHelper.ErpDbModels;
using GKQC.DbHelper.ErpViewModels;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace GKQC.DbHelper.Services
{
    public class ErpService
    {
        /// <summary>
        /// 调试
        /// </summary>
        /// <param name="dbContext"></param>
        /// <returns></returns>
        public static string Test(AllDbContext dbContext )
        {
            string s = "success";
#if NOORACLE
            return s;
#endif

            dbContext.ErpDb.Ado.GetDataTable("select * from dual");

            return s;
        }

        /// <summary>
        /// 獲取Bom
        /// </summary>
        /// <param name="dbContext"></param>
        /// <param name="pn">主料號</param>
        /// <returns></returns>
        public static List<bmb_view> GetBom(AllDbContext dbContext, string pn)
        {
#if NOORACLE
            return new List<bmb_view>();
#endif
            return dbContext.ErpDb.Queryable<bma_view, bmb_view>((a, b) => new object[] {
                JoinType.Left,a.bma01==b.bmb01})
                .Where((a,b)=> a.bma01 == pn
                    && a.bmaacti == "Y" 
                    && (b.bmb05 > dbContext.ErpDb.GetDate().Date || SqlFunc.IsNullOrEmpty(b.bmb05))
                    && (b.bmb04 < dbContext.ErpDb.GetDate().Date || SqlFunc.IsNullOrEmpty(b.bmb04))
                    )
                .Select<bmb_view>().ToList();
        }

        /// <summary>
        /// 獲取工單備料儅
        /// </summary>
        /// <param name="dbContext"></param>
        /// <param name="sfb01">工單單號</param>
        /// <returns></returns>
        public static List<sfa_view> GetWoBom(AllDbContext dbContext, string sfb01)
        {
#if NOORACLE
            return new List<sfa_view>();
#endif

            return dbContext.ErpDb.Queryable<sfa_view, sfb_view>((a, b) => new object[] {
                JoinType.Left,a.sfa01==b.sfb01})
                .Where((a, b) => b.sfb01 == sfb01)
                .OrderBy((a,b)=> a.sfa06, OrderByType.Asc)
                .Select<sfa_view>().ToList();

        }

        /// <summary>
        /// 獲取工單發料底稿
        /// </summary>
        /// <param name="dbContext"></param>
        /// <param name="sfb01">工單單號</param>
        /// <returns></returns>
        public static List<sfs_view> GetSfs(AllDbContext dbContext, string sfs03)
        {
#if NOORACLE
            return new List<sfs_view>();
#endif
            return dbContext.ErpDb.Queryable<sfs_view>()
                .Where(a => a.sfs03 == sfs03)
                .ToList();
        }

        /// <summary>
        /// 獲取工單主档
        /// </summary>
        /// <param name="dbContext"></param>
        /// <param name="sfb01">工單單號</param>
        /// <returns></returns>
        public static SfbView GetSfbView(AllDbContext dbContext, string sfb01)
        {
#if NOORACLE
            return null;
#endif
                var list =  dbContext.ErpDb.Queryable<sfb_view, occ_view>((b, c) => new object[] {
                JoinType.Left,b.ta_sfb08==c.occ01})
                    .Where((b, c) => b.sfb01 == sfb01)
                    .Select<SfbView>().ToList();

            if (list != null && list.Count > 0)
                return list[0];
            else return null;

        }

        /// <summary>
        /// 获取料号资料
        /// </summary>
        /// <param name="dbContext"></param>
        /// <param name="ima01">料号</param>
        /// <returns></returns>
        public static ima_view GetIma(AllDbContext dbContext, string ima01)
        {
#if NOORACLE 
            return null;
#endif

            return dbContext.ErpDb.Queryable<ima_view>()
                .Where(t => t.ima01 == ima01)
                .ToList().FirstOrDefault();
        }

        /// <summary>
        /// 获取流程卡
        /// </summary>
        /// <param name="dbContext"></param>
        /// <param name="ta_shb01">流程卡号</param>
        /// <param name="shb081">作业编号</param>
        /// <param name="shb05">工单号</param>
        /// <returns></returns>
        public static ShbView GetShb(AllDbContext dbContext, string ta_shb01,string shb081,string shb05)
        {
#if NOORACLE
            return null;
#endif
            ShbView result = null;
            try
            {
                var list = dbContext.ErpDb.Queryable<shb_view, sfb_view, occ_view>(
                (a, b, c) => new object[] {
                    JoinType.Left, a.shb05 == b.sfb01
                    ,JoinType.Left, b.ta_sfb08 == c.occ01 }
                )
                .Where((a, b, c) => a.shb05 == shb05 && a.ta_shb01 == ta_shb01 && a.shb081 == shb081)
                .Select<ShbView>().ToList();

                if (list != null && list.Count > 0)
                    return list[0];
            }
            catch(Exception ex)
            {
                result = dbContext.ErpDb.Queryable<shb_view, sfb_view, occ_view>(
                (a, b, c) => new object[] {
                    JoinType.Left, a.shb05 == b.sfb01
                    ,JoinType.Left, b.ta_sfb08 == c.occ01 }
                )
                .Where((a, b, c) => a.shb05 == shb05 && a.ta_shb01 == ta_shb01 && a.shb081 == shb081)
                .Select<ShbView>().First();
            }
            if(result != null)
            {
                return result;
            }
            else
            {
                try
                {
                    result = dbContext.ErpDb.Queryable<shb_view, sfb_view, occ_view>(
                    (a, b, c) => new object[] {
                    JoinType.Left, a.shb05 == b.sfb01
                    ,JoinType.Left, b.ta_sfb08 == c.occ01 }
                    )
                    .Where((a, b, c) => a.shb05 == shb05 && a.ta_shb01 == ta_shb01)
                    .OrderBy((a, b, c) => a.shb02, OrderByType.Desc)
                    .OrderBy((a, b, c) => a.shb021, OrderByType.Desc)
                    .Select<ShbView>().First();
                }
                catch(Exception ex)
                {
                    result = dbContext.ErpDb.Queryable<shb_view, sfb_view, occ_view>(
                    (a, b, c) => new object[] {
                    JoinType.Left, a.shb05 == b.sfb01
                    ,JoinType.Left, b.ta_sfb08 == c.occ01 }
                    )
                    .Where((a, b, c) => a.shb05 == shb05 && a.ta_shb01 == ta_shb01)
                    .OrderBy((a, b, c) => a.shb02, OrderByType.Desc)
                    .OrderBy((a, b, c) => a.shb021, OrderByType.Desc)
                    .Select<ShbView>().First();
                }

                if (result != null) return result;
                else
                {
                    try
                    {
                        return dbContext.ErpDb.Queryable<sfb_view, occ_view>(
                            (b, c) => new object[] {
                            JoinType.Left, b.sfb01 == shb05
                            ,JoinType.Left, b.ta_sfb08 == c.occ01 }
                            )
                            .Where((b, c) => b.sfb01 == shb05)
                            .Select((b, c) => new ShbView()
                            {
                                shb01 = b.sfb01,
                                shb05 = b.sfb01,
                                shb082 = string.Empty,
                                shb09 = string.Empty,
                                shb10 = b.sfb05,
                                shb081 = string.Empty
                            })
                            .First();
                    }
                    catch(Exception ex)
                    {
                        return dbContext.ErpDb.Queryable<sfb_view, occ_view>(
                            (b, c) => new object[] {
                            JoinType.Left, b.sfb01 == shb05
                            ,JoinType.Left, b.ta_sfb08 == c.occ01 }
                            )
                            .Where((b, c) => b.sfb01 == shb05)
                            .Select((b, c) => new ShbView()
                            {
                                shb01 = b.sfb01,
                                shb05 = b.sfb01,
                                shb082 = string.Empty,
                                shb09 = string.Empty,
                                shb10 = b.sfb05,
                                shb081 = string.Empty
                            })
                            .First();
                    }
                }
            }

        }

        /// <summary>
        /// 获取FQC检测标准
        /// </summary>
        /// <param name="qcd01">料号</param>
        /// <returns></returns>
        public static List<qcd_view> GetFqcQcd(AllDbContext dbContext, string qcd01)
        {
#if NOORACLE
            return new List<qcd_view>();
#endif
            return null; //todo: FQC资料没有整理好，暂时屏蔽掉 
            return dbContext.ErpDb.Queryable<qcd_view>()
            .Where(t => t.qcd01 == qcd01 && t.qcd02.StartsWith("F"))
            .ToList();
        }

        /// <summary>
        /// 获取OQC检测标准 
        /// </summary>
        /// <param name="qcd01">料号</param>
        /// <param name="qcdud01">客户代号</param>
        /// <returns></returns>
        public static List<qcd_view> GetOqcQcd(AllDbContext dbContext, string qcd01,string qcdud01)
        {
#if NOORACLE
            return new List<qcd_view>();
#endif
            return new List<qcd_view>();
            //todo: OQC标准缺字段

            return dbContext.ErpDb.Queryable<qcd_view>()
            .Where(t => ((t.qcd01 == qcd01 && t.qcdud01== qcdud01) || (t.qcd01 == qcd01 && SqlFunc.IsNullOrEmpty(t.qcdud01))) 
                    && t.qcd02.StartsWith("O"))
            .OrderBy(t=>t.qcd02, OrderByType.Asc)
            .OrderBy(t => t.qcdud01, OrderByType.Asc)
            .ToList();
        }

        /// <summary>
        /// 获取出通单明细
        /// </summary>
        /// <param name="dbContext"></param>
        /// <param name="ogb01">出通单号</param>
        /// <param name="ogb03">出通单项次</param>
        /// <returns></returns>
        public static OgbView GetOgbView(AllDbContext dbContext, string ogb01,int ogb03)
        {
#if NOORACLE
            return null;
#endif
            return dbContext.ErpDb.Queryable<oga_view, ogb_view, occ_view,ima_view>(
                (a, b, c,d) => new object[] {
                    JoinType.Inner, a.oga01 == b.ogb01
                    ,JoinType.Left, a.oga04 == c.occ01 
                    ,JoinType.Left, b.ogb04 == d.ima01
                }
                )
            .Where((a, b, c) => b.ogb01 == ogb01 && b.ogb03 == ogb03)
            .Select<OgbView>()
            .ToList().FirstOrDefault();
        }

        /// <summary>
        /// 获取出通单明细(按料号汇总)
        /// </summary>
        /// <param name="dbContext"></param>
        /// <param name="ogb01">出通单号</param>
        /// <param name="ogb03">出通单项次</param>
        /// <returns></returns>
        public static OgbViewSumByPn GetOgbViewSumByPn(AllDbContext dbContext, string ogb01, int ogb03)
        {
#if NOORACLE
            return null;
#endif
            var ogbviews = dbContext.ErpDb.Queryable<oga_view, ogb_view, occ_view, ima_view,oea_view>(
                (a, b, c, d,e) => new object[] {
                    JoinType.Inner, a.oga01 == b.ogb01
                    , JoinType.Left, a.oga04 == c.occ01
                    , JoinType.Left, b.ogb04 == d.ima01
                    , JoinType.Left, b.ogb31 == e.oea01
                }
                )
            .Where((a, b, c) => b.ogb01 == ogb01)
            .Select<OgbView>().ToList();

            try
            {
                if (ogbviews != null)
                {
                    var ogbpn = ogbviews.Where(o => o.ogb03 == ogb03);
                    if (ogbpn.Any())
                    {
                        var pn = ogbpn.ToList().FirstOrDefault();
                        if (pn != null)
                        {
                            string pnn = pn.ogb04;
                            return ogbviews.Where(o => o.ogb04 == pnn)
                                .GroupBy(t => new { t.ogb01, t.ogb04, t.ogb11, t.oga04, t.occ02, t.oga03, t.oga031 })
                                .Select(
                                    s => new OgbViewSumByPn
                                    {
                                        ogb01 = s.Key.ogb01,
                                        ogb04 = s.Key.ogb04,
                                        ogb11 = s.Key.ogb11,
                                        oga04 = s.Key.oga04,
                                        occ02 = s.Key.occ02,
                                        oga03 = s.Key.oga03,
                                        oga031 = s.Key.oga031,
                                        ogb16 = s.Sum(t => t.ogb16),
                                        oea10 = s.Max(t=>t.oea10)
                                    }).FirstOrDefault();
                            ;
                        }
                    }
                }
            }
            catch(Exception ex)
            {
                Logger.LogError(ex);
            }

            return null;
        }

        public static int SaveData(AllDbContext dbContext,List<tc_qdf_file> qdfList)
        {
            int flag = 0;

            if (qdfList == null || qdfList.Count == 0) return flag;
            flag = dbContext.ErpDb.Insertable(qdfList).ExecuteCommand();

            return flag;
        }

        /// <summary>
        /// 作废ERP里的检测数据
        /// </summary>
        /// <param name="dbContext"></param>
        /// <param name="sourceNo"></param>
        /// <param name="sno">項次，小於0則更新時不用做條件</param>
        /// <returns></returns>
        public static int CancelFqcErpData(AllDbContext dbContext, string sourceNo,int sno)
        {
            int flag = 0;

#if NOORACLE
            return flag;
#endif

            if (string.IsNullOrWhiteSpace(sourceNo)) return flag;

            string sql1 = "UPDATE \"TC_QDF_FILE\"  SET \"TC_QDFACTI\"='N' WHERE (( \"TC_QDF01\" = :tc_qdf011 ) AND ( \"TC_QDF04\" = :Const2 ))";
            string sql2 = "UPDATE \"TC_QDF_FILE\"  SET \"TC_QDFACTI\"='N' WHERE \"TC_QDF01\" = :tc_qdf011";
            if (sno >= 0)
            {
                //dbContext.ErpDb.Updateable<tc_qdf_file>()
                ////.SetColumns(t => new tc_qdf_file() { tc_qdfacti = "N" })
                //.UpdateColumns(t => new tc_qdf_file() { tc_qdfacti = "N" })
                //.Where(t => t.tc_qdf01 == sourceNo && t.tc_qdf04 == sno)
                //.ExecuteCommand();
                dbContext.ErpDb.Ado.ExecuteCommand(sql1
                    , new SugarParameter[] { 
                        new SugarParameter("tc_qdf011", sourceNo)
                        ,new SugarParameter("Const2", sno)
                        }
                    );
            }
            else
            {
                //dbContext.ErpDb.Updateable<tc_qdf_file>()
                ////.SetColumns(t => new tc_qdf_file() { tc_qdfacti = "N" })
                //.UpdateColumns(t => new tc_qdf_file() { tc_qdfacti = "N" })
                //.Where(t => t.tc_qdf01 == sourceNo)
                //.ExecuteCommand();
                dbContext.ErpDb.Ado.ExecuteCommand(sql2
                    , new SugarParameter[] {
                        new SugarParameter("tc_qdf011", sourceNo)
                        }
                    );
            }

            flag = 1;
            return flag;
        }

        /// <summary>
        /// 作废ERP里的检测数据
        /// </summary>
        /// <param name="dbContext"></param>
        /// <param name="sourceNo"></param>
        /// <param name="sno">項次，小於0則更新時不用做條件</param>
        /// <returns></returns>
        public static int CancelOqcErpData(AllDbContext dbContext, string sourceNo)
        {
            int flag = 0;

#if NOORACLE
            return flag;
#endif

            if (string.IsNullOrWhiteSpace(sourceNo)) return flag;

            string sql2 = "UPDATE \"TC_QDF_FILE\"  SET \"TC_QDFACTI\"='N' WHERE \"TC_QDF01\" = :tc_qdf011";
            dbContext.ErpDb.Ado.ExecuteCommand(sql2
                , new SugarParameter[] {
                                    new SugarParameter("tc_qdf011", sourceNo)
                    }
                );

            
            //dbContext.ErpDb.Updateable<tc_qdf_file>()
            //    .SetColumns(t => new tc_qdf_file() { tc_qdfacti = "N" })
            //    .Where(t => t.tc_qdf01 == sourceNo)
            //    .ExecuteCommand();

            flag = 1;
            return flag;
        }

        /// <summary>
        /// 通过客户名称模糊查询客户代号，查询结果只有一条记录就去客户代号，否则返回空字符串
        /// </summary>
        /// <param name="dbContext"></param>
        /// <param name="customerName"></param>
        /// <returns></returns>
        public static string GetCustomerCodeLikeName(AllDbContext dbContext, string customerName)
        {
            string customerCode = string.Empty;

#if NOORACLE
            return customerCode;
#endif

            var c = dbContext.ErpDb.Queryable<occ_view>()
                .Where(t => SqlFunc.Contains(t.occ02, customerName));
            if(c.Any())
            {
                if(c.Count()==0)
                {
                    customerCode = c.ToList().First().occ01;
                }
            }


            return customerCode;
        }

        /// <summary>
        /// 從工單備註中獲取客戶名稱
        /// </summary>
        /// <param name="sfw01">工單號</param>
        /// <returns></returns>
        public static string GetCustomerNameBySfw(AllDbContext dbContext, string sfw01)
        {
            string customerName = string.Empty;
#if NOORACLE
            return string.Empty;
#endif

            List<sfw_view> sfws = null;

                sfws = sfws = dbContext.ErpDb.Queryable<sfw_view>()
                .Where(t => t.sfw01 == sfw01)
                .ToList();


            if (sfws!= null && sfws.Count>1)
            {
                string sfw2 = sfws[1].sfw03;
                if(!string.IsNullOrWhiteSpace(sfw2))
                {
                    string[] sfw22 = sfw2.Split(' ');
                    if (sfw22 != null && sfw22.Length > 1)
                        customerName = sfw22[0];
                }
            }

            return customerName;
        }
    }
}
